package com.itheima.knowledge.manager.dao;

import com.itheima.knowledge.manager.domain.Catalog;
import com.itheima.knowledge.manager.domain.Knowledge;
import com.itheima.knowledge.manager.domain.NewTable;
import com.itheima.knowledge.manager.domain.english;
import com.itheima.knowledge.manager.utils.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;

/*
查找目录数据表
 */
public class KnowledgeDao {
/*
String sql = "select * from emp";
        List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
        for (Emp emp : list) {
            System.out.println(emp);
 */
   private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    public ArrayList<Catalog> findCatalog(){
        String sql = "select * from catalog";

        ArrayList<Catalog> list = (ArrayList<Catalog>) template.query(sql, new BeanPropertyRowMapper<Catalog>(Catalog.class));
        /*for (Emp emp : list) {
            System.out.println(emp);*/
       /* ArrayList<Catalog> catalogs = new ArrayList<>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://123.57.235.125:3306/study?characterEncoding=UTF-8&userSSL=false&serverTimezone=Hongkong","root","123456qwe~");
            //3.定义sql
            String sql = "select * from catalog;";
            //4.获取执行sql的对象
            stmt = conn.createStatement();
            //5.执行sql
            rs = stmt.executeQuery(sql);
            //6.遍历结果集，封装对象，装载集合
            Catalog cat = null;
            while(rs.next()){
                //获取数据
                String logid = rs.getString("logid");
                String logknow = rs.getString("logknow");


                // 创建emp对象,并赋值
                cat = new Catalog();
                cat.setLogid(logid);
                cat.setLogknow(logknow);


                //装载集合
                catalogs.add(cat);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }*/
        return list;

    }

    public void deleteCatalog(String id) {
        ArrayList<Catalog> array = findCatalog();
        String know = null;
        for (int i = 0; i < array.size(); i++) {
            Catalog cat = array.get(i);
            if (cat.getLogid()!=null&&cat.getLogid().equals(id));
            know = cat.getLogknow();
        }
        String sql = "delete from catalog where logid = ?";
         template.update(sql, id);
        //System.out.println(count);
       /* Connection conn = null;
        Statement stmt = null;
        try {
            //1. 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接对象
            conn = DriverManager.getConnection("jdbc:mysql://123.57.235.125:3306/study?characterEncoding=UTF-8&userSSL=false&serverTimezone=Hongkong","root","123456qwe~");
            //3.定义sql
            String sql  = "delete from catalog where logid = '"+id+"';";
            //4.获取执行sql对象
            stmt = conn.createStatement();
            //5.执行sql
            int count = stmt.executeUpdate(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7.释放资源

            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }*/
        deleteKnowtemp(know);
    }

    public void deleteKnowtemp(String know) {
        if (know!=null) {
            template.execute("drop table "+know);
       /* Connection conn = null;
        Statement stmt = null;
            try {
                //1. 注册驱动
                Class.forName("com.mysql.cj.jdbc.Driver");
                //2.获取连接对象
                conn = DriverManager.getConnection("jdbc:mysql://123.57.235.125:3306/study?characterEncoding=UTF-8&userSSL=false&serverTimezone=Hongkong","root","123456qwe~");
                //3.定义sql
                String sql  = "drop table "+know+";";
                //4.获取执行sql对象
                stmt = conn.createStatement();
                //5.执行sql
                int count = stmt.executeUpdate(sql);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                //7.释放资源

                if(stmt != null){
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

                if(conn != null){
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }*/
        }else {
            System.out.println("删除失败,查无此表,请检查数据库");
        }

    }

    public void updataCatalog(String id, String know,String oldKnow) {

        //2. 定义sql
        String sql = "update catalog set logknow = ? where logid = ? ";
        //3. 执行sql
       int count = template.update(sql,know,id);
        //System.out.println(count);
       /* Connection conn = null;
        Statement stmt = null;
        try {
            //1. 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接对象
            conn = DriverManager.getConnection("jdbc:mysql://123.57.235.125:3306/study?characterEncoding=UTF-8&userSSL=false&serverTimezone=Hongkong","root","123456qwe~");
            //3.定义sql
            String sql  = "update catalog set logknow ='"+know+"' where logid ='"+id+"';";
            //4.获取执行sql对象
            stmt = conn.createStatement();
            //5.执行sql
            int count = stmt.executeUpdate(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7.释放资源

            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }*/
        updataKnowtemp(id,know,oldKnow);

    }

    public void updataKnowtemp(String id,String know,String oldKnow) {
        if (know!=null) {
            template.execute("alter table "+oldKnow+" rename to "+know);
           /* Connection conn = null;
            Statement stmt = null;
            try {
                //1. 注册驱动
                Class.forName("com.mysql.cj.jdbc.Driver");
                //2.获取连接对象
                conn = DriverManager.getConnection("jdbc:mysql://123.57.235.125:3306/study?characterEncoding=UTF-8&userSSL=false&serverTimezone=Hongkong","root","123456qwe~");
                //3.定义sql
                String sql  = "alter table "+oldKnow+" rename to "+know+";";
                //4.获取执行sql对象
                stmt = conn.createStatement();
                //5.执行sql
                int count = stmt.executeUpdate(sql);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                //7.释放资源

                if(stmt != null){
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

                if(conn != null){
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }*/
        }else {
            System.out.println("修改失败,查无此表,请检查数据库");
        }

    }

    public void addCatalog(Catalog catalog) {
        String id = catalog.getLogid();
        String know = catalog.getLogknow();
        String sql = "insert into catalog(logid,logknow) values(?,?)";
        int count = template.update(sql, id, know);
        //System.out.println(count);
        /*Statement stmt = null;
        Connection conn = null;
        try {
            //1. 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2. 定义sql
            String sql = "insert into catalog values(" + "'" + id + "'" + "," + "'" + know + "');";
            //3.获取Connection对象
            conn = DriverManager.getConnection("jdbc:mysql://123.57.235.125:3306/study?characterEncoding=UTF-8&userSSL=false&serverTimezone=Hongkong", "root", "123456qwe~");
            //4.获取执行sql的对象 Statement
            stmt = conn.createStatement();
            //5.执行sql
            int count = stmt.executeUpdate(sql);//影响的行数
            //6.处理结果
           *//* System.out.println(count);
            if(count > 0){
                System.out.println("添加成功！");
            }else{
                System.out.println("添加失败！");
            }*//*

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //stmt.close();
            //7. 释放资源
            //避免空指针异常
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }*/
        addKnowTemp(know);
    }

    public void addKnowTemp(String know) {
        template.execute("CREATE TABLE "+know+"  (knid varchar(20), sketch varchar(500),textpart varchar(2000),remark varchar(500),insert_time varchar(40))");
        /*Statement stmt = null;
        Connection conn = null;
        try {
            //1. 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2. 定义sql
            String sql = "create table "+know+" ( knid varchar(20), sketch varchar(500),textpart varchar(2000),remark varchar(500),insert_time varchar(40));";
            //3.获取Connection对象
            conn = DriverManager.getConnection("jdbc:mysql://123.57.235.125:3306/study?characterEncoding=UTF-8&userSSL=false&serverTimezone=Hongkong","root","123456qwe~");
            //4.获取执行sql的对象 Statement
            stmt = conn.createStatement();
            //5.执行sql
            int count = stmt.executeUpdate(sql);//影响的行数

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //stmt.close();
            //7. 释放资源
            //避免空指针异常
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }*/
    }

    public ArrayList<Knowledge> findAllLogid(String know) {

        String sql = "select * from "+know;
        ArrayList<Knowledge> knowledges = (ArrayList<Knowledge>) template.query(sql, new BeanPropertyRowMapper<Knowledge>(Knowledge.class));
      // System.out.println(knowledges.get(1).getInsert_time());
        //String sql1 = "select * from "+know;
       // List<Knowledge> list = template.query(sql, new BeanPropertyRowMapper<>(Knowledge.class));
       /* for (Knowledge emp : list) {
            System.out.println(emp);
        }*/
       /* ArrayList<Knowledge> knowledges = new ArrayList<>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql://123.57.235.125:3306/study?characterEncoding=UTF-8&userSSL=false&serverTimezone=Hongkong","root","123456qwe~");
            //3.定义sql
            String sql = "select * from "+know+";";
            //4.获取执行sql的对象
            stmt = conn.createStatement();
            //5.执行sql
            rs = stmt.executeQuery(sql);
            //6.遍历结果集，封装对象，装载集合
            Knowledge kn = null;
            while(rs.next()){
                //获取数据
                String knid = rs.getString("knid");
                String sketch = rs.getString("sketch");
                String textpart = rs.getString("textpart");
                String remark = rs.getString("remark");
                String time = rs.getString("insert_time");//new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(rs.getString("insert_time"));


                // 创建emp对象,并赋值
                kn = new Knowledge();
                kn.setKnid(knid);
                kn.setSketch(sketch);
                kn.setTextpart(textpart);
                kn.setRemark(remark);
                kn.setInsert_time(time);
                //装载集合
                knowledges.add(kn);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }*/
        return knowledges;
    }

    public void addKnowledge(Knowledge knd,String know) {
        String knid = knd.getKnid();
        String sketch = knd.getSketch();
        String textpart = knd.getTextpart();
        String remark = knd.getRemark();
        String insert_time =  knd.getInsert_time();
        //System.out.println(time);
        //new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(knd.getTime())
        String sql = "insert into "+know+" values(?,?,?,?,?)";
        int count = template.update(sql, knid, sketch,textpart,remark,insert_time);
        /*Statement stmt = null;
        Connection conn = null;
        try {
            //1. 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2. 定义sql
            String sql = "insert into "+know+" values('"+knid+"','"+sketch+"','"+textpart+"','"+remark+"','"+time+"');";
            //3.获取Connection对象
            conn = DriverManager.getConnection("jdbc:mysql://123.57.235.125:3306/study?characterEncoding=UTF-8&userSSL=false&serverTimezone=Hongkong", "root", "123456qwe~");
            //4.获取执行sql的对象 Statement
            stmt = conn.createStatement();
            //5.执行sql
            int count = stmt.executeUpdate(sql);//影响的行数

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //stmt.close();
            //7. 释放资源
            //避免空指针异常
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }*/
    }

    public void deleteKnowledge(String id, String know) {
        String sql = "delete from "+know+" where knid = ?";
        template.update(sql, id);
        /*Connection conn = null;
        Statement stmt = null;
        try {
            //1. 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接对象
            conn = DriverManager.getConnection("jdbc:mysql://123.57.235.125:3306/study?characterEncoding=UTF-8&userSSL=false&serverTimezone=Hongkong","root","123456qwe~");
            //3.定义sql
            String sql  = "delete from "+know+" where knid = '"+id+"';";
            //4.获取执行sql对象
            stmt = conn.createStatement();
            //5.执行sql
            int count = stmt.executeUpdate(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7.释放资源

            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }*/
    }

    public void updataKnowledge(String id, Knowledge knowledge,String know,int cho) {
        String knid =knowledge.getKnid();
        String sketch = knowledge.getSketch();
        String textpart = knowledge.getTextpart();
        String remark  = knowledge.getRemark();
        String time = knowledge.getInsert_time();
        if(cho==1){
            //2. 定义sql
            String sql = "update "+know+" set sketch = ?,textpart = ? where knid = ?";
            //3. 执行sql
             template.update(sql,sketch,textpart,id);
        }else {
            String sql = "update "+know+" set sketch = ?,remark = ? where knid = ?";
            //3. 执行sql
            template.update(sql,sketch,remark ,id);
        }
       /* Connection conn = null;
        Statement stmt = null;
        try {
            //1. 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接对象
            conn = DriverManager.getConnection("jdbc:mysql://123.57.235.125:3306/study?characterEncoding=UTF-8&userSSL=false&serverTimezone=Hongkong","root","123456qwe~");
            //3.定义sql
            String sql;
            if(cho==1){
                 sql  = "update "+know+" set sketch ='"+sketch+"',textpart = '"+textpart+"'where knid ='"+id+"';";
            }else {
                 sql  = "update "+know+" set sketch ='"+sketch+"',remark = '"+remark+"'where knid ='"+id+"';";
            }
            //String sql  = "update "+know+" set sketch ='"+sketch+"',textpart = '"+textpart+"',remark = '"+remark+"'where knid ='"+id+"';";

            //4.获取执行sql对象
            stmt = conn.createStatement();
            //5.执行sql
            int count = stmt.executeUpdate(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7.释放资源

            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
*/
    }

    public ArrayList<NewTable> exercis(int ch) {
        ArrayList<Catalog> arrayCata = findCatalog();
        ArrayList<String> arrayName = new ArrayList<>();
        ArrayList<Knowledge> arrayNew =new ArrayList<>();
        ArrayList<NewTable> newTables =new ArrayList<>();
        for (Catalog catalog : arrayCata) {
            String oid = catalog.getLogid();
           // !oid.equals("e")&&!oid.equals("c")&&!oid.equals("u")&&!oid.equals("d")&&!oid.equals("n")
            if (oid.charAt(0)<97) {
                arrayName.add(catalog.getLogknow());
            }
        }
       // System.out.println(arrayName);
       // int j=0;
        for (String know : arrayName) {
            ArrayList<Knowledge> arrayTable = findAllLogid(know);
            //System.out.println(arrayTable);
            int[] arr = rundamNumber(ch,arrayTable.size());
            for (int i = 0; i < arr.length; i++) {
                arrayNew.add(arrayTable.get(arr[i]));
            }
              // System.out.println(arrayTable.get(arr[i]));

               //j++;

        }
        for (int i = 0; i < arrayNew.size(); i++) {
            NewTable newTable = new NewTable();
            Knowledge knowledge = arrayNew.get(i);
            newTable.setNewid(knowledge.getKnid());
            newTable.setNewsk(knowledge.getSketch());
            newTable.setNewtext(knowledge.getTextpart());
            newTable.setNewre(knowledge.getRemark());
            newTables.add(newTable);
        }
        return newTables;
    }
    public int[] rundamNumber(int ch,int len){
        int[] arr = new int[ch];
        Random ran =new Random();
        for (int i = 0; i < ch; i++) {
            boolean flag = false;
            int temp= ran.nextInt(len);
            for (int j = i-1; j >= 0; j--) {
                if (temp==arr[j]){
                    flag = true;
                    i--;
                    break;
                }
            }
            if (!flag){
                arr[i]=temp;
            }
        }
       // System.out.println(Arrays.toString(arr));
        return arr;
    }

    public void newable(ArrayList<NewTable> array) {
        for (NewTable newTable : array) {
            String newid = newTable.getNewid();
            String newsk = newTable.getNewsk();
            String newtext = newTable.getNewtext();
            String newre = newTable.getNewre();
            String ask = newTable.getAsk();

            String sql = "insert into newtable values(?,?,?,?,?)";
            int count = template.update(sql, newid, newsk,newtext,newre,ask);
        }

    }

    public ArrayList<NewTable> getNewTable() {
        String sql = "select * from newtable";
        ArrayList<NewTable> newTable = (ArrayList<NewTable>) template.query(sql, new BeanPropertyRowMapper<>(NewTable.class));
        template.execute("TRUNCATE TABLE newtable");
        return newTable;


    }

    public ArrayList<english> Ctranslate(String english,int c) {
        ArrayList<Knowledge> neng = new ArrayList<>();
        ArrayList<Knowledge> eng = findAllLogid(english);
        ArrayList<english> englishes = new ArrayList<>();

        int[] arr = rundamNumber(c,eng.size());
        //System.out.println(Arrays.toString(arr));
        for (int i = 0; i < arr.length; i++) {
            neng.add(eng.get(arr[i]));
        }

        for (int i = 0; i < neng.size(); i++) {
            english english1 = new english();
            Knowledge knowledge = neng.get(i);
            english1.setCword(knowledge.getTextpart());
            english1.setEword(knowledge.getSketch());
            englishes.add(english1);
        }
        return englishes;

    }

    public void newenglish(ArrayList<english> eng1) {
        for (english english : eng1) {
             String cword =english.getCword();
            String eword = english.getEword();
            String nword= english.getNword();

            String sql = "insert into newenglish values(?,?,?)";
            int count = template.update(sql, cword, eword,nword);
        }
    }
    public ArrayList<english> getNewenglish() {
        String sql = "select * from newenglish";
        ArrayList<english> englishs = (ArrayList<english>) template.query(sql, new BeanPropertyRowMapper<>(english.class));
        template.execute("TRUNCATE TABLE newenglish");
        return englishs;


    }




   /* public void addPerson(Person per) {
        ArrayList<Person> person = new ArrayList<>();
        String id = per.getId();
        String name =per.getName();
        String age = per.getAge();
        String birthday = per.getBirthday();
        Statement stmt = null;
        Connection conn = null;
        try {
            //1. 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2. 定义sql
            String sql = "insert into "+flag+" values("+"'"+id+"'"+","+"'"+name+"'"+","+"'"+age+"'"+","+"'"+birthday+"'"+")";
            //3.获取Connection对象
            conn = DriverManager.getConnection("jdbc:mysql://192.168.21.130:3306/studyjava?characterEncoding=UTF-8","root","123456Qwe~");
            //4.获取执行sql的对象 Statement
            stmt = conn.createStatement();
            //5.执行sql
            int count = stmt.executeUpdate(sql);//影响的行数
            //6.处理结果
           *//* System.out.println(count);
            if(count > 0){
                System.out.println("添加成功！");
            }else{
                System.out.println("添加失败！");
            }*//*

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //stmt.close();
            //7. 释放资源
            //避免空指针异常
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
    }*/
}
